package com.lanqiao.guimei.dao;

import com.lanqiao.guimei.commons.Page;
import com.lanqiao.guimei.vo.GoodItem;
import com.lanqiao.guimei.vo.Product;

import com.lanqiao.guimei.vo.SaleProd;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @author StarsOne
 * @date Create in  2019-8-14 0014 11:10:56
 * @description
 */
public class SaleProdDao extends BaseDao {

    //product表 列起别名
    //p.prod_No prodNo,p.ctgr_id ctgrId,p.ven_id venId,p.stk_id stkId,p.prod_name prodName,price,type,descr,image

    /**
     * 查询新品上架全部商品
     * @return
     */
    public List<Product> queryNew() {
        try {
            return runner.query("select p.prod_No prodNo,p.ctgr_id ctgrId,p.ven_id venId,p.stk_id stkId,p.prod_name prodName,price,type,descr,image from PRODUCT p,SALE_PROD s where p.PROD_NO=s.PROD_NO and current_date-s.STAR_TIME>0 order by current_date-s.STAR_TIME", new BeanListHandler<>(Product.class));
        } catch (SQLException e) {
            System.err.println("查询新品上架商品错误："+e.getMessage());
        }
        return null;
    }

    /**
     * 查询全部抢购商品
     * @return
     */
    public List<Product> queryCheap() {
        try {
            return runner.query("select p.prod_No prodNo,p.ctgr_id ctgrId,p.ven_id venId,p.stk_id stkId,p.prod_name prodName,price,type,descr,image from PRODUCT p,SALE_PROD s where p.PROD_NO=s.PROD_NO  order by p.PRICE-s.REAL_PRICE desc", new BeanListHandler<>(Product.class));
        } catch (SQLException e) {
            System.err.println("查询抢购商品错误："+e.getMessage());
        }
        return null;
    }

    /**
     * 新品的记录数
     * @return
     */
    public int queryAllRowNew() {
        try {
            return Integer.valueOf(runner.query("select count(1) from PRODUCT p,SALE_PROD s where p.PROD_NO=s.PROD_NO and current_date-s.STAR_TIME>0 order by current_date-s.STAR_TIME", new ScalarHandler<>()).toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 抢购商品总记录数
     * @return
     */
    public int queryAllRowHot() {
        try {
            return Integer.valueOf(runner.query("select count(1) from PRODUCT p,SALE_PROD s where p.PROD_NO=s.PROD_NO  order by p.PRICE-s.REAL_PRICE desc", new ScalarHandler<>()).toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 查询在售商品表sale_prod的总记录
     * @return
     */
    public int queryAllRow() {
        try {
            return Integer.valueOf(runner.query("select count(1) from SALE_PROD", new ScalarHandler<>()).toString());
        } catch (SQLException e) {
            System.err.println("查询在售商品表Sale_Prod 总行数失败：" + e.getMessage());
        }
        return 0;
    }

    /**
     * 分页查询 新品商品
     * @param page
     * @return
     */
    public List<Product> queryNew(Page<Product> page) {
        try {
            return runner.query("select prod_No prodNo,ctgr_id ctgrId,ven_id venId,stk_id stkId,prod_name prodName,price,type,descr,image from (select ps.*,rownum rn from (select p.* from PRODUCT p,SALE_PROD s where p.PROD_NO=s.PROD_NO and current_date-s.STAR_TIME>0 order by current_date-s.STAR_TIME) ps where ROWNUM<=?) where rn>?", new BeanListHandler<>(Product.class), page.getLastIndex(), page.getFirstIndex() - 1);
        } catch (SQLException e) {
            System.err.println("查询抢购商品错误："+e.getMessage());
        }
        return null;
    }

    /**
     * 查询抢购商品 (打折的价格较多的排前面）
     * @return
     */
    public List<GoodItem> queryHot() {
        try {
            return runner.query("select prod_No prodNo,ctgr_id ctgrId,ven_id venId,stk_id stkId,prod_name prodName,price,type,descr,image from (select p.* from PRODUCT p,SALE_PROD s where p.PROD_NO=s.PROD_NO  order by p.PRICE-s.REAL_PRICE desc) ", new BeanListHandler<>(GoodItem.class));
        } catch (SQLException e) {
            System.err.println("查询抢购商品错误："+e.getMessage());
        }
        return null;
    }

    /**
     * 分页查询抢购商品 (打折的价格较多的排前面）
     * @param page
     * @return
     */
    public List<Product> queryHot(Page<Product> page) {
        try {
            return runner.query("select prod_No prodNo,ctgr_id ctgrId,ven_id venId,stk_id stkId,prod_name prodName,price,type,descr,image from (select ps.*,rownum rn from (select p.* from PRODUCT p,SALE_PROD s where p.PROD_NO=s.PROD_NO  order by p.PRICE-s.REAL_PRICE desc) ps where ROWNUM<=?) where rn>?", new BeanListHandler<>(Product.class), page.getLastIndex(), page.getFirstIndex() - 1);
        } catch (SQLException e) {
            System.err.println("查询抢购商品错误："+e.getMessage());
        }
        return null;
    }

    /**
     * 模糊查询
     * @param name
     * @return
     */
    public List<Product> queryByName(String name) {
        try {
           return runner.query("select p.prod_No prodNo,p.ctgr_id ctgrId,p.ven_id venId,p.stk_id stkId,p.prod_name prodName,price,type,descr,image from SALE_PROD s,PRODUCT p where s.PROD_NO =p.PROD_NO and PROD_NAME like ?", new BeanListHandler<>(Product.class), "%" + name + "%");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 查询大类别的总记录
     * @param type
     * @return
     */
    public int queryAllRowType(String type) {
        try {
            return Integer.valueOf(runner.query("select count(1) from PRODUCT p,SALE_PROD s where  p.PROD_NO = s.PROD_NO and p.CTGR_ID in (select CTGR_ID from CATEGORY where  BIG_CTGR = ?)", new ScalarHandler<>(), type).toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public Product queryByProdNo(String prodNo) {
        try {
            return runner.query("select prod_No prodNo,ctgr_id ctgrId,ven_id venId,stk_id stkId,prod_name prodName,price,type,descr,image from product where prod_no = ?", new BeanHandler<>(Product.class), prodNo);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 根据大分类分页查询
     * @param type 大分类
     * @return
     */
    public List<Product> queryByType(Page<Product> page, String type) {
        try {
            return runner.query("select prod_No prodNo,ctgr_id ctgrId,ven_id venId,stk_id stkId,prod_name prodName,price,type,descr,image from (select ps.*,ROWNUM rn from (select p.* from PRODUCT p,SALE_PROD s where  p.PROD_NO = s.PROD_NO and p.CTGR_ID in (select CTGR_ID from CATEGORY where  BIG_CTGR = ?)) ps where ROWNUM<=?) where rn>?", new BeanListHandler<>(Product.class), type,page.getLastIndex(),page.getFirstIndex()-1);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 小分类
     * @param id
     * @return
     */
    public int queryAllRowCtgrId(int id) {
        try {
            return Integer.valueOf(runner.query("select count(1) from PRODUCT p,SALE_PROD s where  p.PROD_NO = s.PROD_NO and p.CTGR_ID =?", new ScalarHandler<>(), id).toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public List<Product> queryByCtgrId(int id) {
        try {
            return runner.query("select p.prod_No prodNo,p.ctgr_id ctgrId,p.ven_id venId,p.stk_id stkId,p.prod_name prodName,price,type,descr,image from PRODUCT p,SALE_PROD s where  p.PROD_NO = s.PROD_NO and p.CTGR_ID =?)", new BeanListHandler<>(Product.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<Product> queryByCtgrId(Page<Product> page,int id) {
        try {
            return runner.query("select prod_No prodNo,ctgr_id ctgrId,ven_id venId,stk_id stkId,prod_name prodName,price,type,descr,image from (select ps.*,ROWNUM rn from (select p.* from PRODUCT p,SALE_PROD s where  p.PROD_NO = s.PROD_NO and p.CTGR_ID =?) ps where ROWNUM<=?) where rn>?", new BeanListHandler<>(Product.class), id,page.getLastIndex(),page.getFirstIndex()-1);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }



    public SaleProd findByProdNo(String prodNo){
       try {
           return runner.query("select ID,PROD_NO prodNo,SALER_ID salerId, STK_ID stkId, REAL_PRICE realPrice, CARRIAGE, STAR_TIME starTime, END_TIME endTime, PROD_SKIN prodSkin, LOCATION,STATUS from SALE_PROD where prod_no=?",new BeanHandler<>(SaleProd.class),prodNo);
       } catch (SQLException e) {
           e.printStackTrace();
       }
       return null;
    }

    



}
